package samsung.sds.system.export.service;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.TreeMap;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import samsung.sds.survey.surveyStatistics.model.QualitySurveyStatisticsResultEntity;
import samsung.sds.survey.surveyStatistics.model.QuestionEntity;
import samsung.sds.survey.surveyStatistics.model.QuestionOptionEntity;
import samsung.sds.survey.surveyStatistics.model.SurveyEntity;
import samsung.sds.survey.surveyStatistics.util.SurveyStaticsUtils;

@Service
public class ExportServiceImpl implements ExportService {

	public <T> String createExcel(List<T> xls) throws IOException,
			IllegalArgumentException, IllegalAccessException,
			InvocationTargetException {

		if (xls.size() <= 0) {
			return "";
		}
		int columnNameCount = xls.get(0).getClass().getDeclaredFields().length;
		HSSFWorkbook hwb = new HSSFWorkbook();
		T xlsDto = null;

		HSSFSheet sheet = hwb.createSheet("data");
		HSSFRow firstrow = sheet.createRow(0); // 下标为0的行开始
		HSSFCell[] firstcell = new HSSFCell[columnNameCount];
		Field[] field = xls.get(0).getClass().getDeclaredFields();
		for (int j = 0; j < columnNameCount; j++) {
			firstcell[j] = firstrow.createCell(j);
			firstcell[j]
					.setCellValue(new HSSFRichTextString(field[j].getName()));
		}

		for (int i = 0; i < xls.size(); i++) {
			HSSFRow row = sheet.createRow(i + 1);
			xlsDto = xls.get(i);
			for (int column = 0; column < field.length; column++) {
				HSSFCell cell = row.createCell(column);
				for (Method m : xlsDto.getClass().getDeclaredMethods()) {
					if (m.getName().startsWith("get")
							&& m.getName()
									.toLowerCase()
									.contains(
											field[column].getName()
													.toLowerCase())) {

						if (m.invoke(xlsDto) != null) {

							cell.setCellValue(m.invoke(xlsDto).toString());
						}
					}
				}
			}
		}

		long l = System.currentTimeMillis();
		String path = System.getProperty("user.dir");
		File file = new File(path + "\\temp\\", "data" + l + ".xls");
		if (file.exists()) {
			file.delete();
		} else {
			file.getParentFile().mkdirs();
			file.createNewFile();
		}
		System.out.println(file.getAbsolutePath() + "======="
				+ System.currentTimeMillis());
		OutputStream stream = new FileOutputStream(file);
		hwb.write(stream);
		stream.close();
		System.out.println("导出成功");
		return file.getAbsolutePath();
	}

	public <T> String createQuestionExcel(Map<String, List<String>> xls)
			throws IOException, IllegalArgumentException,
			IllegalAccessException, InvocationTargetException {
		Map<String, String> map = new HashMap<String, String>();
		for (Map.Entry<String, List<String>> en : xls.entrySet()) {
			map.put(en.getKey(), en.getValue().size() + "");
			if (en.getValue().size() > 0) {
				for (String s : en.getValue()) {

					map.put("", s);
				}
			}

		}
		if (xls.size() <= 0) {
			return "";
		}

		HSSFWorkbook hwb = new HSSFWorkbook();
		HSSFSheet sheet = hwb.createSheet("data");
		HSSFRow firstrow = sheet.createRow(0); // 下标为0的行开始
		HSSFCell[] firstcell = new HSSFCell[2];

		// 第一列

		firstcell[0] = firstrow.createCell(0);
		firstcell[0].setCellValue(new HSSFRichTextString("QuestionItem"));

		// 第二列

		firstcell[1] = firstrow.createCell(1);
		firstcell[1].setCellValue(new HSSFRichTextString("QuestionItem"));

		int i = 0;
		// int j = 0;
		for (Entry<String, List<String>> entry : xls.entrySet()) {
			i++;
			HSSFRow row = sheet.createRow(i);

			HSSFCell cell = row.createCell(0);

			cell.setCellValue(entry.getKey());

			HSSFCell cell1 = row.createCell(1);

			cell1.setCellValue(entry.getValue().size());
			if (entry.getValue().size() > 0) {
				for (String str : entry.getValue()) {
					i++;
					HSSFRow row1 = sheet.createRow(i);
					HSSFCell cell2 = row1.createCell(0);

					cell2.setCellValue(" ");

					HSSFCell cell3 = row1.createCell(1);

					cell3.setCellValue(str);
				}

			}

		}

		long l = System.currentTimeMillis();
		String path = System.getProperty("user.dir");
		File file = new File(path + "\\temp\\", "data" + l + ".xls");
		if (file.exists()) {
			file.delete();
		} else {
			file.getParentFile().mkdirs();
			file.createNewFile();
		}
		System.out.println(file.getAbsolutePath() + "======="
				+ System.currentTimeMillis());
		OutputStream stream = new FileOutputStream(file);
		hwb.write(stream);
		stream.close();
		System.out.println("导出成功");
		return file.getAbsolutePath();
	}

	public <T> String createExcelForUserAnswerStatus(List<T> xls,
			String[] strDetail) throws IOException, IllegalArgumentException,
			IllegalAccessException, InvocationTargetException {
		if (xls.size() <= 0) {
			return "";
		}
		int columnNameCount = xls.get(0).getClass().getDeclaredFields().length;
		HSSFWorkbook hwb = new HSSFWorkbook();
		T xlsDto = null;
		HSSFSheet sheet = hwb.createSheet("data");
		HSSFColor color = SurveyStaticsUtils.setColor(hwb, (byte) 0,
				(byte) 176, (byte) 240);
		// 导出table中，向左居中，向右居中不统一，则两个style切换使用
		HSSFCellStyle cellStyleForAlignCenter = hwb.createCellStyle();
		cellStyleForAlignCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 定义cellstyle，文字居中

		HSSFCellStyle cellStyleForAlignLeft = hwb.createCellStyle();
		cellStyleForAlignLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 明细数据区域定义cellstyle，表头文字居左，加背景色
		cellStyleForAlignLeft.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充单元格
		cellStyleForAlignLeft.setFillForegroundColor(color.getIndex());

		HSSFCellStyle cellStyleForAlignLeftForContext = hwb.createCellStyle();
		cellStyleForAlignLeftForContext.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 定义cellstyle，文字居左

		HSSFCellStyle cellStyleForAlignRight = hwb.createCellStyle();
		cellStyleForAlignRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);// 定义cellstyle，文字居右

		HSSFCellStyle cellStyleForHead = hwb.createCellStyle();
		cellStyleForHead.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 汇总数据区域定义cellstyle，表头文字居中，加背景色，文字变大
		cellStyleForHead.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充单元格
		cellStyleForHead.setFillForegroundColor(color.getIndex());
		HSSFFont font = hwb.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short) 14);// 设置字体大小
		cellStyleForHead.setFont(font);
		for (int i = 0; i < 6; i++) {
			HSSFRow detailRow = sheet.createRow(i);
			if (i == 0) {
				detailRow.createCell(0).setCellValue("用户投票结果统计");
				detailRow.getCell(0).setCellStyle(cellStyleForHead);
				detailRow.setHeight((short) 500);
				sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
			} else if (i == 1)
				detailRow.createCell(0).setCellValue("问卷名称");
			else if (i == 2)
				detailRow.createCell(0).setCellValue("截止时间");
			else if (i == 3)
				detailRow.createCell(0).setCellValue("邀请人数");
			else if (i == 4)
				detailRow.createCell(0).setCellValue("投票人数");
			else
				detailRow.createCell(0).setCellValue("开始时间");
			if (i != 0) {
				detailRow.createCell(1).setCellValue(strDetail[i - 1]);
				detailRow.getCell(1).setCellStyle(cellStyleForAlignCenter);
				sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 6));
			}
		}
		HSSFRow firstrow = sheet.createRow(6); // 下标为0的行开始
		HSSFCell[] firstcell = new HSSFCell[7];
		Field[] field = xls.get(0).getClass().getDeclaredFields();

		int flag = 0;
		Map<String, String> properyToColumnMap = new HashMap<String, String>();
		properyToColumnMap.put("rowNum", "NO");
		properyToColumnMap.put("userName", "USERNAME");
		properyToColumnMap.put("cnName", "CN_NAME");
		properyToColumnMap.put("email", "EMAIL");
		properyToColumnMap.put("deptName", "DEPT_NAME");
		properyToColumnMap.put("status", "STATUS");
		properyToColumnMap.put("createTime", "CREATE_TIME");
		for (int j = 0; j < columnNameCount; j++) {
			if (properyToColumnMap.keySet().contains(field[j].getName())) {
				firstcell[flag] = firstrow.createCell(flag);
				firstcell[flag].setCellValue(new HSSFRichTextString(
						properyToColumnMap.get(field[j].getName())));
				firstcell[flag].setCellStyle(cellStyleForAlignLeft);
				flag++;
			}
		}
		for (int i = 0; i < xls.size(); i++) {
			HSSFRow row = sheet.createRow(i + 7);
			xlsDto = xls.get(i);
			int flag2 = 0;
			for (int column = 0; column < field.length; column++) {
				if (properyToColumnMap.keySet().contains(
						field[column].getName())) {
					HSSFCell cell = row.createCell(flag2);
					if (flag2 == 0)
						cell.setCellStyle(cellStyleForAlignRight);
					else
						cell.setCellStyle(cellStyleForAlignLeftForContext);

					for (Method m : xlsDto.getClass().getDeclaredMethods()) {
						if ((m.getName().contains("UserName")
								|| m.getName().contains("RowNum")
								|| m.getName().contains("CnName")
								|| m.getName().contains("Email")
								|| m.getName().contains("DeptName")
								|| m.getName().contains("Status") || m
								.getName().contains("CreateTime"))
								&& m.getName().startsWith("get")
								&& m.getName()
										.toLowerCase()
										.contains(
												field[column].getName()
														.toLowerCase())) {
							if (m.invoke(xlsDto) != null) {

								cell.setCellValue(m.invoke(xlsDto).toString());
							}
						}
					}
					flag2++;
				}
			}
		}

		long l = System.currentTimeMillis();
		String path = System.getProperty("user.dir");
		File file = new File(path + "\\temp\\", "data" + l + ".xls");
		if (file.exists()) {
			file.delete();
		} else {
			file.getParentFile().mkdirs();
			file.createNewFile();
		}
		System.out.println(file.getAbsolutePath() + "======="
				+ System.currentTimeMillis());
		OutputStream stream = new FileOutputStream(file);
		hwb.write(stream);
		stream.close();
		System.out.println("导出成功");
		return file.getAbsolutePath();
	}

	/**
	 * YN型调查问卷统计表
	 */
	public <T> String createExcelForYNStatus(Map<String, Object> map,
			List<QualitySurveyStatisticsResultEntity> questionList,
			List<QualitySurveyStatisticsResultEntity> voteResultList)
			throws IOException, IllegalArgumentException,
			IllegalAccessException, InvocationTargetException {
		// 创建excel文件
		HSSFWorkbook hwb = new HSSFWorkbook();
		// 生成表头 问卷相关信息
		HSSFSheet sheet = hwb.createSheet("new sheet");// 创建excel表格
		// 样式
		HSSFColor color = SurveyStaticsUtils.setColor(hwb, (byte) 0,
				(byte) 176, (byte) 240);
		// 导出table中，向左居中，向右居中不统一，则两个style切换使用
		HSSFCellStyle cellStyleForAlignCenter = hwb.createCellStyle();
		cellStyleForAlignCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 定义cellstyle，文字居中

		HSSFCellStyle cellStyleForAlignLeft = hwb.createCellStyle();
		cellStyleForAlignLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 明细数据区域定义cellstyle，表头文字居左，加背景色
		cellStyleForAlignLeft.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充单元格
		cellStyleForAlignLeft.setFillForegroundColor(color.getIndex());

		HSSFCellStyle cellStyleForAlignLeftForContext = hwb.createCellStyle();
		cellStyleForAlignLeftForContext.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 定义cellstyle，文字居左

		HSSFCellStyle cellStyleForAlignRight = hwb.createCellStyle();
		cellStyleForAlignRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);// 定义cellstyle，文字居右

		HSSFCellStyle cellStyleForHead = hwb.createCellStyle();
		cellStyleForHead.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 汇总数据区域定义cellstyle，表头文字居中，加背景色，文字变大
		cellStyleForHead.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充单元格
		cellStyleForHead.setFillForegroundColor(color.getIndex());
		HSSFFont font = hwb.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short) 20);// 设置字体大小
		cellStyleForHead.setFont(font);

		// 设置行高
		sheet.setDefaultRowHeightInPoints(10);
		// 设置列宽
		sheet.setDefaultColumnWidth(18);

		int combineCellNum = questionList.size() + 2;// 需要合并的单元格数
		for (int i = 0; i < 6; i++) {
			HSSFRow detailRow = sheet.createRow(i);
			if (i == 0) {
				// 设置样式
				HSSFCell[] questioncell = new HSSFCell[1000];
				questioncell[0] = detailRow.createCell(0);
				detailRow.getCell(0).setCellStyle(cellStyleForHead);
				detailRow
						.setHeight((short) SurveyStaticsUtils.FIRST_ROW_HEIGHT);
				questioncell[0].setCellValue("用户投票统计结果");
				sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
						combineCellNum - 1));
			} else if (i == 1) {
				detailRow.createCell(0).setCellValue("问卷名称");
				detailRow.getCell(0).setCellStyle(cellStyleForAlignRight);
				detailRow.createCell(1).setCellValue(
						map.get("survey_name").toString());
				sheet.addMergedRegion(new CellRangeAddress(1, 1, 1,
						combineCellNum - 1));
			} else if (i == 2) {
				detailRow.createCell(0).setCellValue("截止时间");
				detailRow.getCell(0).setCellStyle(cellStyleForAlignRight);
				detailRow.createCell(1).setCellValue(
						map.get("end_time").toString());
				sheet.addMergedRegion(new CellRangeAddress(2, 2, 1,
						combineCellNum - 1));
			} else if (i == 3) {
				detailRow.createCell(0).setCellValue("邀请人数");
				detailRow.getCell(0).setCellStyle(cellStyleForAlignRight);
				detailRow.createCell(1).setCellValue(
						map.get("ask_num").toString());
				sheet.addMergedRegion(new CellRangeAddress(3, 3, 1,
						combineCellNum - 1));
			} else if (i == 4) {
				detailRow.createCell(0).setCellValue("已投票人数");
				detailRow.getCell(0).setCellStyle(cellStyleForAlignRight);
				detailRow.createCell(1).setCellValue(
						map.get("vote_num").toString());
				sheet.addMergedRegion(new CellRangeAddress(4, 4, 1,
						combineCellNum - 1));
			} else if (i == 5) {
				detailRow.createCell(0).setCellValue("生成时间");
				detailRow.getCell(0).setCellStyle(cellStyleForAlignRight);
				detailRow.createCell(1).setCellValue(
						map.get("create_date").toString());
				sheet.addMergedRegion(new CellRangeAddress(5, 5, 1,
						combineCellNum - 1));
			}
		}
		// 生成表头 问卷问题
		HSSFRow questionRow = sheet.createRow(6); // 下标为0的行开始

		HSSFCell[] questioncell = new HSSFCell[1000];

		questioncell[0] = questionRow.createCell(0);
		questioncell[0].setCellStyle(cellStyleForAlignLeft);
		questioncell[0].setCellValue(new HSSFRichTextString("用户姓名"));
		questioncell[1] = questionRow.createCell(1);
		questioncell[1].setCellStyle(cellStyleForAlignLeft);
		questioncell[1].setCellValue(new HSSFRichTextString("投票时间"));

		// 拼接问题表头
		for (int j = 0; j < questionList.size(); j++) {
			questioncell[j + 2] = questionRow.createCell(j + 2);
			questioncell[j + 2].setCellStyle(cellStyleForAlignLeft);
			questioncell[j + 2].setCellValue(new HSSFRichTextString(
					questionList.get(j).getQuestion_content()));
		}

		// 排序
		for (int i = 0; i < voteResultList.size() - 1; i++) {
			for (int j = 1; j < voteResultList.size() - i; j++) {
				String time = voteResultList.get(j - 1).getLast_Modify_Time();
				String lastTime = voteResultList.get(j).getLast_Modify_Time();
				if (time.compareTo(lastTime) > 0) {
					QualitySurveyStatisticsResultEntity entity = voteResultList
							.get(j - 1);
					voteResultList.set(j - 1, voteResultList.get(j));
					voteResultList.set(j, entity);
				}
			}
		}

		// 生成结果列表
		for (int k = 0; k < voteResultList.size(); k++) {
			HSSFRow answerRow = sheet.createRow(k + 7);
			HSSFCell[] answerCell = new HSSFCell[1000];
			answerCell[0] = answerRow.createCell(0);
			answerCell[0].setCellValue(new HSSFRichTextString(voteResultList
					.get(k).getUsername()));
			answerCell[0].setCellStyle(cellStyleForAlignLeftForContext);
			answerCell[1] = answerRow.createCell(1);
			String voteTime = voteResultList.get(k).getLast_Modify_Time();
			answerCell[1].setCellValue(new HSSFRichTextString(voteTime));
			answerCell[1].setCellStyle(cellStyleForAlignCenter);
			for (int n = 0; n < questionList.size(); n++) {
				boolean flag = false;
				answerCell[n + 2] = answerRow.createCell(n + 2);
				for (int m = 0; m < voteResultList.get(k).getAnswers().size(); m++) {
					if (questionList.get(n).getSurvey_id() == voteResultList
							.get(k).getAnswers().get(n).getSurvey_Id()) {
						flag = true;
						answerCell[n + 2].setCellValue(new HSSFRichTextString(
								getRealAnswer(voteResultList.get(k)
										.getAnswers().get(m).getAnswer())));
					}
				}
				if (!flag) {
					answerCell[n + 2].setCellValue("N/A");
				}
				answerCell[n + 2].setCellStyle(cellStyleForAlignCenter);
			}
		}
		// 把拼接好的内容传入，开始输出文件
		File file = generateFile(hwb);
		return file.getAbsolutePath();
	}

	/**
	 * 将答案转意
	 * 
	 * @param param
	 * @return
	 */
	private String getRealAnswer(String param) {
		if ("1".equals(param)) {
			return "Y";
		} else if ("0".equals(param)) {
			return "N";
		}
		return param;

	}

	/**
	 * 将拼接好的内容传入，导出
	 * 
	 * @param hwb
	 * @return
	 * @throws IOException
	 * @throws FileNotFoundException
	 */
	private File generateFile(HSSFWorkbook hwb) throws IOException,
			FileNotFoundException {
		long l = System.currentTimeMillis();
		String path = System.getProperty("user.dir");
		File file = new File(path + "\\temp\\", "data" + l + ".xls");
		if (file.exists()) {
			file.delete();
		} else {
			file.getParentFile().mkdirs();
			file.createNewFile();
		}
		OutputStream stream = new FileOutputStream(file);
		hwb.write(stream);
		stream.close();
		return file;
	}

	/**
	 * 导出数字类型投票结果
	 */
	public <T> String createExcelForQualitySurvey(Map<String, Object> map,
			List<QualitySurveyStatisticsResultEntity> questionList,
			List<QualitySurveyStatisticsResultEntity> voteResultList)
			throws IOException, IllegalArgumentException,
			IllegalAccessException, InvocationTargetException {
		// 创建excel文件
		HSSFWorkbook hwb = new HSSFWorkbook();
		// 生成表头 问卷相关信息
		HSSFSheet sheet = hwb.createSheet("new sheet");// 创建excel表格
		// 样式
		HSSFColor color = SurveyStaticsUtils.setColor(hwb, (byte) 0,
				(byte) 176, (byte) 240);
		// 导出table中，向左居中，向右居中不统一，则两个style切换使用
		HSSFCellStyle cellStyleForAlignCenter = hwb.createCellStyle();
		cellStyleForAlignCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 定义cellstyle，文字居中

		HSSFCellStyle cellStyleForAlignLeft = hwb.createCellStyle();
		cellStyleForAlignLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 明细数据区域定义cellstyle，表头文字居左，加背景色
		cellStyleForAlignLeft.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充单元格
		cellStyleForAlignLeft.setFillForegroundColor(color.getIndex());

		HSSFCellStyle cellStyleForAlignLeftForContext = hwb.createCellStyle();
		cellStyleForAlignLeftForContext.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 定义cellstyle，文字居左

		HSSFCellStyle cellStyleForAlignRight = hwb.createCellStyle();
		cellStyleForAlignRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);// 定义cellstyle，文字居右

		HSSFCellStyle cellStyleForHead = hwb.createCellStyle();
		cellStyleForHead.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 汇总数据区域定义cellstyle，表头文字居中，加背景色，文字变大
		cellStyleForHead.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充单元格
		cellStyleForHead.setFillForegroundColor(color.getIndex());
		HSSFFont font = hwb.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short) 20);// 设置字体大小
		cellStyleForHead.setFont(font);

		// 设置行高
		sheet.setDefaultRowHeightInPoints(10);
		// 设置列宽
		sheet.setDefaultColumnWidth(18);

		int combineCellNum = questionList.size() + 4;// 需要合并的单元格数
		for (int i = 0; i < 6; i++) {
			HSSFRow detailRow = sheet.createRow(i);
			if (i == 0) {
				// 设置样式
				HSSFCell[] questioncell = new HSSFCell[50];
				questioncell[0] = detailRow.createCell(0);
				detailRow.getCell(0).setCellStyle(cellStyleForHead);
				detailRow
						.setHeight((short) SurveyStaticsUtils.FIRST_ROW_HEIGHT);
				questioncell[0].setCellValue("用户投票统计结果");
				sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
						combineCellNum - 1));
			} else if (i == 1) {
				detailRow.createCell(0).setCellValue("问卷名称");
				detailRow.getCell(0).setCellStyle(cellStyleForAlignRight);
				detailRow.createCell(1).setCellValue(
						map.get("survey_name").toString());
				sheet.addMergedRegion(new CellRangeAddress(1, 1, 1,
						combineCellNum - 1));
			} else if (i == 2) {
				detailRow.createCell(0).setCellValue("截止时间");
				detailRow.getCell(0).setCellStyle(cellStyleForAlignRight);
				detailRow.createCell(1).setCellValue(
						map.get("end_time").toString());
				sheet.addMergedRegion(new CellRangeAddress(2, 2, 1,
						combineCellNum - 1));
			} else if (i == 3) {
				detailRow.createCell(0).setCellValue("邀请人数");
				detailRow.getCell(0).setCellStyle(cellStyleForAlignRight);
				detailRow.createCell(1).setCellValue(
						map.get("ask_num").toString());
				sheet.addMergedRegion(new CellRangeAddress(3, 3, 1,
						combineCellNum - 1));
			} else if (i == 4) {
				detailRow.createCell(0).setCellValue("已投票人数");
				detailRow.getCell(0).setCellStyle(cellStyleForAlignRight);
				detailRow.createCell(1).setCellValue(
						map.get("vote_num").toString());
				sheet.addMergedRegion(new CellRangeAddress(4, 4, 1,
						combineCellNum - 1));
			} else if (i == 5) {
				detailRow.createCell(0).setCellValue("生成时间");
				detailRow.getCell(0).setCellStyle(cellStyleForAlignRight);
				detailRow.createCell(1).setCellValue(
						map.get("create_date").toString());
				sheet.addMergedRegion(new CellRangeAddress(5, 5, 1,
						combineCellNum - 1));
			}
		}
		// 生成表头 问卷问题
		HSSFRow questionRow = sheet.createRow(6); // 下标为0的行开始

		HSSFCell[] questioncell = new HSSFCell[50];

		questioncell[0] = questionRow.createCell(0);
		questioncell[0].setCellStyle(cellStyleForAlignLeft);
		questioncell[0].setCellValue(new HSSFRichTextString("用户姓名"));
		questioncell[1] = questionRow.createCell(1);
		questioncell[1].setCellStyle(cellStyleForAlignLeft);
		questioncell[1].setCellValue(new HSSFRichTextString("中文名称"));
		questioncell[2] = questionRow.createCell(2);
		questioncell[2].setCellStyle(cellStyleForAlignLeft);
		questioncell[2].setCellValue(new HSSFRichTextString("部门名称"));
		questioncell[3] = questionRow.createCell(3);
		questioncell[3].setCellStyle(cellStyleForAlignLeft);
		questioncell[3].setCellValue(new HSSFRichTextString("投票时间"));

		// 拼接问题表头
		for (int j = 0; j < questionList.size(); j++) {
			questioncell[j + 4] = questionRow.createCell(j + 4);
			questioncell[j + 4].setCellStyle(cellStyleForAlignLeft);
			questioncell[j + 4].setCellValue(new HSSFRichTextString(
					questionList.get(j).getQuestion_content()));
		}

		// 排序
		for (int i = 0; i < voteResultList.size() - 1; i++) {
			for (int j = 1; j < voteResultList.size() - i; j++) {
				String time = voteResultList.get(j - 1).getLast_Modify_Time();
				String lastTime = voteResultList.get(j).getLast_Modify_Time();
				if (time.compareTo(lastTime) > 0) {
					QualitySurveyStatisticsResultEntity entity = voteResultList
							.get(j - 1);
					voteResultList.set(j - 1, voteResultList.get(j));
					voteResultList.set(j, entity);
				}
			}
		}

		// 生成结果列表
		for (int k = 0; k < voteResultList.size(); k++) {
			HSSFRow answerRow = sheet.createRow(k + 7);
			HSSFCell[] answerCell = new HSSFCell[50];
			answerCell[0] = answerRow.createCell(0);
			answerCell[0].setCellValue(new HSSFRichTextString(voteResultList.get(k).getUsername()));
			answerCell[0].setCellStyle(cellStyleForAlignLeftForContext);
			answerCell[1] = answerRow.createCell(1);
			answerCell[1].setCellValue(new HSSFRichTextString(voteResultList.get(k).getCn_name()));
			answerCell[1].setCellStyle(cellStyleForAlignCenter);
			answerCell[2] = answerRow.createCell(2);
			answerCell[2].setCellValue(new HSSFRichTextString(voteResultList.get(k).getDept_name()));
			answerCell[2].setCellStyle(cellStyleForAlignLeftForContext);
			answerCell[3] = answerRow.createCell(3);
			String voteTime = voteResultList.get(k).getLast_Modify_Time();
			answerCell[3].setCellValue(new HSSFRichTextString(voteTime));
			answerCell[3].setCellStyle(cellStyleForAlignLeftForContext);
			for (int n = 0; n < questionList.size(); n++) {
				boolean flag = false;
				answerCell[n + 4] = answerRow.createCell(n + 4);
				for (int m = 0; m < voteResultList.get(k).getAnswers().size(); m++) {

					if ((questionList.get(n).getId().toString()).equals(voteResultList
							.get(k).getAnswers().get(m).getQuestion_Id().toString())) {
						flag = true;
						answerCell[n + 4].setCellValue(new HSSFRichTextString(
								voteResultList.get(k).getAnswers().get(m)
										.getAnswer()));
					}
				}
				if (!flag) {
					answerCell[n + 4].setCellValue("N/A");
				}
				answerCell[n + 4].setCellStyle(cellStyleForAlignCenter);
			}
		}
		// 把拼接好的内容传入，开始输出文件
		File file = generateFile(hwb);
		return file.getAbsolutePath();
	}

	/**
	 * 创建组合统计Excel
	 * 
	 * @param xls
	 * @param survey
	 * @param paramMap
	 * @param questionOrderArray
	 * @param <T>
	 * @return
	 * @throws IOException
	 */
	public <T> String createGroupStaticSurveyStatics(
			Map<String, List<String>> xls, SurveyEntity survey,
			Map<String, String> paramMap, String[] questionOrderArray)
			throws IOException {
		HSSFWorkbook workBook = new HSSFWorkbook();
		HSSFSheet sheet = workBook.createSheet(SurveyStaticsUtils.SHEET_NAME);
		for (int i = 0; i < 5; i++) {
			sheet.setColumnWidth(i, SurveyStaticsUtils.EXCEL_COLUMN_WIDTH);
		}
		HSSFCellStyle style = workBook.createCellStyle(); // 样式对象
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		int allQuestionOptionsCount = countAllQuestionOptionsCountInSurvey(
				survey, questionOrderArray);
		int combinedRows = allQuestionOptionsCount;
		generateFifthContent(SurveyStaticsUtils.GROUPING_STATICS_RESULT, sheet,
				style, survey, workBook, paramMap);
		HSSFRow row = sheet.createRow(5);
		createStaticsCell(style, row, 0,
				SurveyStaticsUtils.GROUPING_STATIC_TOPIC);
		sheet.addMergedRegion(new CellRangeAddress(5, 5 + combinedRows - 1, 0,
				0));
		int initCombination = 5;
		for (QuestionEntity question : survey.getQuestion()) {
			if (ArrayUtils.contains(questionOrderArray,
					String.valueOf(question.getQuestionOrder()))) {
				if (initCombination != 5) {
					HSSFRow hssfRow = sheet.createRow(initCombination);
					combineCell(sheet, style, question, hssfRow);
				} else {
					combineCell(sheet, style, question, row);
				}
				char c = 'A';
				for (QuestionOptionEntity questionOption : question
						.getQuestionOptionList()) {
					initCombination++;
					HSSFRow row1 = sheet.createRow(initCombination);
					createStaticsCell(style, row1, 2, String.valueOf(c));
					createStaticsCell(style, row1, 3,
							questionOption.getOptionName());
					c++;
				}
				initCombination++;
			}
		}
		generateStatics(xls, sheet, style, combinedRows);
		generateTimeCell(sheet, style, 5 + combinedRows - 1);
		File file = generateFile(workBook);
		return file.getAbsolutePath();
	}

	/**
	 * 合并单元格
	 * 
	 * @param sheet
	 * @param style
	 * @param question
	 * @param hssfRow
	 */
	private void combineCell(HSSFSheet sheet, HSSFCellStyle style,
			QuestionEntity question, HSSFRow hssfRow) {
		createStaticsCell(style, hssfRow, 1,
				String.valueOf(question.getQuestionOrder()));
		createStaticsCell(style, hssfRow, 2, question.getQuestionContent());
		sheet.addMergedRegion(new CellRangeAddress(hssfRow.getRowNum(), hssfRow
				.getRowNum(), 2, 3));
		sheet.addMergedRegion(new CellRangeAddress(hssfRow.getRowNum(),
				question.getQuestionOptionList().size() + hssfRow.getRowNum(),
				1, 1));
	}

	/**
	 * 生成对应行的对应列的单元格
	 * 
	 * @param style
	 * @param titleRow
	 * @param cellNumber
	 * @param cellValue
	 */
	private void createStaticsCell(HSSFCellStyle style, HSSFRow titleRow,
			int cellNumber, String cellValue) {
		HSSFCell titleCell = titleRow.createCell(cellNumber);
		titleCell.setCellValue(cellValue);
		titleCell.setCellStyle(style);
	}

	/**
	 * 生成组合项的统计结果
	 * 
	 * @param xls
	 * @param sheet
	 * @param style
	 * @param combinedRows
	 */
	private void generateStatics(Map<String, List<String>> xls,
			HSSFSheet sheet, HSSFCellStyle style, int combinedRows) {
		int i = 5 + combinedRows + 1;
		HSSFRow titleRow = sheet.createRow(i);
		HSSFCellStyle firstRowStyle = SurveyStaticsUtils.getSpecialCellStyle(
				sheet.getWorkbook(), 300);
		createStaticsCell(firstRowStyle, titleRow, 0,
				SurveyStaticsUtils.GROUPING_ITEM);
		createStaticsCell(firstRowStyle, titleRow, 1,
				SurveyStaticsUtils.GROUPING_RESULT);
		createStaticsCell(firstRowStyle, titleRow, 3,
				SurveyStaticsUtils.GROUPING_ITEM);
		createStaticsCell(firstRowStyle, titleRow, 4,
				SurveyStaticsUtils.USER_NAME);
		Map<String, List<String>> treeMap = new TreeMap<String, List<String>>(
				new Comparator<String>() {
					@Override
					public int compare(String o1, String o2) {
						if (SurveyStaticsUtils.containsAnyLetter(o1)) {
							return SurveyStaticsUtils
									.replaceSpecialStrInString(
											SurveyStaticsUtils
													.replaceSpecialKey(o1))
									.compareTo(
											SurveyStaticsUtils
													.replaceSpecialStrInString(SurveyStaticsUtils
															.replaceSpecialKey(o2)));
						} else {
							return SurveyStaticsUtils
									.replaceSpecialStrInString(o2)
									.compareTo(
											SurveyStaticsUtils
													.replaceSpecialStrInString(o1));
						}
					}
				});
		treeMap.putAll(xls);
		for (Entry<String, List<String>> entry : treeMap.entrySet()) {
			i++;
			HSSFRow tempRow = sheet.createRow(i);
			createStaticsCell(style, tempRow, 0,
					SurveyStaticsUtils.replaceKey(entry.getKey()));
			createStaticsCell(style, tempRow, 1,
					String.valueOf(entry.getValue().size()));
			createStaticsCell(style, tempRow, 3,
					SurveyStaticsUtils.replaceKey(entry.getKey()));
			String cellValue = dealWithCellValue(entry);
			createStaticsCell(style, tempRow, 4, cellValue);
		}
	}

	/**
	 * 处理用户名Cell值
	 * 
	 * @param entry
	 * @return
	 */
	private String dealWithCellValue(Entry<String, List<String>> entry) {
		StringBuffer stringBuffer = new StringBuffer();
		if (!CollectionUtils.isEmpty(entry.getValue())) {
			for (String str : entry.getValue()) {
				stringBuffer.append(str).append(",");
			}
			if (!StringUtils.isEmpty(stringBuffer.toString())) {
				return stringBuffer.toString().substring(0,
						stringBuffer.length() - 1);
			}
		} else {
			return SurveyStaticsUtils.NON_USERNAME;
		}
		return "";
	}

	/**
	 * 生成时间单元格的生成
	 * 
	 * @param sheet
	 * @param style
	 * @param combinedRows
	 */
	private void generateTimeCell(HSSFSheet sheet, HSSFCellStyle style,
			int combinedRows) {
		HSSFRow timeRow = sheet.createRow(combinedRows + 1);
		createStaticsCell(style, timeRow, 0, SurveyStaticsUtils.GENERATE_TIME);
		createStaticsCell(style, timeRow, 1,
				SurveyStaticsUtils.formatDate(new Date()));
	}

	/**
	 * 生成前五行的excel单元格
	 * 
	 * @param sheet
	 * @param style
	 * @param survey
	 * @param workBook
	 * @param paramMap
	 */
	private void generateFifthContent(String titleName, HSSFSheet sheet,
			HSSFCellStyle style, SurveyEntity survey, HSSFWorkbook workBook,
			Map<String, String> paramMap) {
		HSSFCellStyle firstRowStyle = SurveyStaticsUtils.getSpecialCellStyle(
				workBook, SurveyStaticsUtils.FIRST_ROW_FONT_SIZE);
		HSSFRow firstRow = sheet.createRow(0);
		firstRow.setHeight((short) SurveyStaticsUtils.FIRST_ROW_HEIGHT);
		createStaticsCell(firstRowStyle, firstRow, 0, titleName);
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
		Map<String, String> map = new HashMap<String, String>();
		map.put(SurveyStaticsUtils.SURVEY_NAME, survey.getSurveyName());
		map.put(SurveyStaticsUtils.END_TIME,
				SurveyStaticsUtils.formatDate(survey.getEndDate()));
		map.put(SurveyStaticsUtils.INVITEE_COUNT,
				paramMap.get("userCountBySurvey"));
		map.put(SurveyStaticsUtils.VOTED_PERSONS,
				paramMap.get("votedUserCountBySurvey"));
		int i = 1;
		for (Entry<String, String> entry : map.entrySet()) {
			HSSFRow row = sheet.createRow(i);
			createStaticsCell(style, row, 0, entry.getKey());
			createStaticsCell(style, row, 1, entry.getValue());
			i++;
		}
	}

	/**
	 * 计算出选中的选项的所有的问题选项的和
	 * 
	 * @param survey
	 * @param questionOrderArray
	 * @return
	 */
	private int countAllQuestionOptionsCountInSurvey(SurveyEntity survey,
			String[] questionOrderArray) {
		int allQuestionOptionsCount = 0;
		for (QuestionEntity question : survey.getQuestion()) {
			if (ArrayUtils.contains(questionOrderArray,
					String.valueOf(question.getQuestionOrder())))
				allQuestionOptionsCount += question.getQuestionOptionList()
						.size() + 1;
		}
		return allQuestionOptionsCount;
	}

	/**
	 * 解决导出文件名不能为中文问题
	 * 
	 * @throws UnsupportedEncodingException
	 * */
	public static String toUtf8String(String s, HttpServletRequest request)
			throws UnsupportedEncodingException {
		String agent = request.getHeader("USER-AGENT");
		if (null != agent && -1 != agent.indexOf("MSIE") || null != agent
				&& -1 != agent.indexOf("Trident")) {
			// IE
			s = java.net.URLEncoder.encode(s + ".xls", "UTF8");
		} else if (null != agent && -1 != agent.indexOf("Mozilla")) {
			// 火狐,chrome等
			s = new String((s + ".xls").getBytes("utf-8"), "ISO-8859-1");
		}
		return s;
	}
}
